/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.caracol.db;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * This class contains the commun methods that are used to interacto with the
 * data base
 * @author Roque Rueda
 */
public class CaracolQueryGenerator {

    private AccesoDatos ad;
    private boolean isOpen = false;
    
    public CaracolQueryGenerator() {
    }

    /**
     * Executes the given query and returns a Vector<Object> as a result from
     * data base
     * @param query the query to executes
     * @return Vector<Object> with the result
     * @throws Exception if an exception is throws it is throw again but this
     * method allwayas close the connection
     */
    public List<Object> executeQuery(String query) throws Exception {
        getOpenConnection();
        try {
            return ad.ejecutarConsulta(query);
        } catch (Exception e) {
            throw new Exception("CaracolQueryGenerator: error al ejecutar consulta: " + e.getMessage());
        } finally {
            closeOpenConnection();
        }
    }

    /**
     * Close the open conexion if exist
     * @throws Exception 
     */
    private void closeOpenConnection() throws Exception {
        if (isOpen) {
            ad.desconectar();
            ad = null;
            isOpen = false;
        }
    }
    
    /**
     * Executes the given string command
     * @param command String command to execute in teh databasee
     * @return number of rows affected in the database
     * @throws Exception if some exeption is throw during the execution this is
     * throw again
     */
    public int executeCommand(String command) throws Exception{
        getOpenConnection();
        try {
            return ad.ejecutarComando(command);
        } catch (Exception e) {
            throw new Exception("CaracolQueryGenerator: error al ejecutar commando: " + e.getMessage());
        } finally {
            closeOpenConnection();
        }
    }
    
    /**
     * Executes the given string command
     * @param command String command to execute in teh databasee
     * @return number of rows affected in the database
     * @throws Exception if some exeption is throw during the execution this is
     * throw again
     */
    public int executeCommandLeaveOpenConnection(String command) throws Exception{
        getOpenConnection();
        try {
            return ad.ejecutarComando(command);
        } catch (Exception e) {
            throw new Exception("CaracolQueryGenerator: error al ejecutar commando: " + e.getMessage());
        }
    }
    
    /**
     * Creates a string to query the database postgresql
     * Crea una cadena para la realizacion de consultas a una base de datos postgresql
     * 
     * @param colums Array of strings that contains all the colums if null is
     * passed this select all the colums in the table
     * @param tableName Name of the table to select
     * @param condition Map<String,String> this is the selection to be aplied
     * in the database and should be in the forma key as column name and value
     * as 'X'value that should contain the database if null no selection is aply
     * @param groupBy this is the group by expression if null no group by is aply
     * @param having having setence to be aply
     * @param orderBy order by setence to be aply
     * @param limit limit if records setence to be aply
     * @return String with the command to execute
     */
    public List<Object> query(String[] colums, String tableName,
            Map<String, String> conditions /* selection should be column as key and 'X'value as value of map */,
            String groupBy, String having, String orderBy, String limit) throws SQLException, Exception{
        try{
            getOpenConnection();
            return ad.query(colums, tableName, conditions, groupBy, having, orderBy, limit);
        }finally{
            closeOpenConnection();
        }
        
    }
    
    /**
     * Ejecuta un consulta a la base de datos mediate un like regresando una lista de objetos 
     * con los valores obtenidos y cerrando la conexion
     * 
     * Executes a query in the database using a like sentence and returns a List of Object with the
     * returned values from the Database and closing the connection
     * 
     * @param colums Array of strings that contains all the colums if null is
     * passed this select all the colums in the table
     * @param tableName Name of the table to select
     * @param condition Map<String,String> this is the selection to be aplied
     * in the database and should be in the forma key as column name and value
     * as 'X'value that should contain the database if null no selection is aply
     * @param groupBy this is the group by expression if null no group by is aply
     * @param having having setence to be aply
     * @param orderBy order by setence to be aply
     * @param limit limit if records setence to be aply
     * @return List of Object with the result
     * @exception SQLException if some database exception occurs
     * @exception Exception if something other than database exception occurs
     */
    public synchronized java.util.List<Object> likeQuery(String[] colums, String tableName,
            Map<String, String> conditions /* selection should be column as key and 'X'value as value of map */,
            String groupBy, String having, String orderBy, String limit) throws SQLException, Exception {
        try{
            getOpenConnection();
            return ad.likeQuery(colums, tableName, conditions, groupBy, having, orderBy, limit);
        }finally{
            closeOpenConnection();
        }
    }
    
    /**
     * Insert a given set of items sort in a map where key is column name and
     * value is the value to insert this doesn't close the connection
     * @param tableName name of the table to be update
     * @param values values to be inserted into te table 
     * @return number of rows affected
     * @throws SQLException if some database exception occurs this is throw
     */
    public int insert (String tableName, Map<String,Object> values) throws SQLException, Exception {
        getOpenConnection();
        return ad.insert(tableName, values);
    }
    
    /**
     * Updates the given table with the values sort in order where key String is column name and
     * value Object is the value to be set as part of a prepared statement this method open and
     * close the active connection
     * @param tableName Name of the table to be updated
     * @param values Map of values that are going to be set this <String, Object>
     * entrys should be in the forma of key = column name part of the table and
     * value = value to be set in the set sentence
     * @param conditions the contiditions to be aply in the update clause if null
     * is passed as value the no conditions is used and all the table is update
     * @return  true if the rows affected is > 0
     * @throws SQLException If some database exception happend
     * @throws Exception If something other than the database throws an exception
     * this is throw again
     */
    public int update (String tableName, Map<String,Object> values, Map<String, Object> conditions) throws SQLException, Exception {        
        try{
            getOpenConnection();
            return ad.update(tableName, values, conditions);
        }
        catch(SQLException sqlex){
            throw  sqlex;
        }
        finally{
            closeOpenConnection();
        }
    }
    
    /**
     * This method deletes rows from the database according the conditions if no
     * conditions are used it deletes all rows from the table so careful
     * @param tableName Name of the table to be delete
     * @param conditions Set of conditions to be apply in the format key as column
     * name and value as the value in the where clause
     * @return number of rows affected
     * @throws SQLException if some sqlexception happends
     * @throws Exception  if something other happends
     */
    public int delete(String tableName, Map<String,Object> conditions) throws SQLException, Exception{
        try{
            getOpenConnection();
            return ad.delete(tableName, conditions);
        }
        catch(SQLException sqlex){
            throw  sqlex;
        }
        finally{
            closeOpenConnection();
        }
    }

    public AccesoDatos getOpenConnection() throws Exception {
        if (ad == null) {
            ad = new AccesoDatos();
            ad.conectar();
            isOpen = true;
        }
        return ad;
    }
    
    
}
